{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['1975 Registration Data - Total New Vehicles by Month', '', '', '', '', '', '', '', '', '', '', '', '', '', '']\n",
      "['Year', '', 'Jan', 'Feb', 'Mar', 'April', 'May', 'June', 'July', 'Aug', 'Sept', 'Oct', 'Nov', 'Dec', 'TOTALS']\n",
      "[2004.0, '', 7235.0, 6921.0, 8820.0, 7047.0, 7738.0, 9738.0, 8494.0, 7885.0, 9225.0, 9186.0, 8748.0, 7755.0, 98792.0]\n",
      "[2017.0, '', 13823.0, 11785.0, 13869.0, 10635.0, 13132.0, 15985.0, 11631.0, '', '', '', '', '', 90860.0]\n",
      "[7235.0, 6921.0, 8820.0, 7047.0, 7738.0, 9738.0, 8494.0, 7885.0, 9225.0, 9186.0, 8748.0, 7755.0]\n",
      "[13823.0, 11785.0, 13869.0, 10635.0, 13132.0, 15985.0, 11631.0, '', '', '', '', '']\n",
      "[7235.0, 6921.0, 8820.0, 7047.0, 7738.0, 9738.0, 8494.0, 7885.0, 9225.0, 9186.0, 8748.0, 7755.0, 7295.0, 7599.0, 9109.0, 7424.0, 8419.0, 10381.0, 8687.0, 9621.0, 9784.0, 9412.0, 8381.0, 7336.0, 7797.0, 7774.0, 9743.0, 6613.0, 8421.0, 8991.0, 8040.0, 8642.0, 9346.0, 9398.0, 8152.0, 7069.0, 8492.0, 7482.0, 9238.0, 6617.0, 8395.0, 8951.0, 8879.0, 8976.0, 9162.0, 10055.0, 8957.0, 7264.0, 9399.0, 8179.0, 8856.0, 7573.0, 7629.0, 9630.0, 7488.0, 7364.0, 8514.0, 9340.0, 6537.0, 6821.0, 6508.0, 5058.0, 6445.0, 4729.0, 5293.0, 6118.0, 5740.0, 5493.0, 7046.0, 6898.0, 5735.0, 4985.0, 6703.0, 5659.0, 6987.0, 5688.0, 6417.0, 8004.0, 5997.0, 6437.0, 7944.0, 7297.0, 7376.0, 5944.0, 7523.0, 6404.0, 8260.0, 5610.0, 6563.0, 7652.0, 6705.0, 7465.0, 7628.0, 7026.0, 7328.0, 6476.0, 8681.0, 7185.0, 8528.0, 7048.0, 8306.0, 10101.0, 8361.0, 8175.0, 8690.0, 9388.0, 8581.0, 7751.0, 9293.0, 7947.0, 9505.0, 8143.0, 8924.0, 10723.0, 9401.0, 9430.0, 10071.0, 10696.0, 10388.0, 8776.0, 10528.0, 9139.0, 11238.0, 8729.0, 10064.0, 12519.0, 10313.0, 10189.0, 11840.0, 12023.0, 11176.0, 9594.0, 11667.0, 9877.0, 11755.0, 9290.0, 10034.0, 13030.0, 10366.0, 11192.0, 12474.0, 12684.0, 11620.0, 10245.0, 11893.0, 10313.0, 12110.0, 9939.0, 11180.0, 13699.0, 11570.0, 12677.0, 13884.0, 14709.0, 13740.0, 11225.0, 13823.0, 11785.0, 13869.0, 10635.0, 13132.0, 15985.0, 11631.0, '', '', '', '', '']\n",
      "[7235.0, 6921.0, 8820.0, 7047.0, 7738.0, 9738.0, 8494.0, 7885.0, 9225.0, 9186.0, 8748.0, 7755.0, 7295.0, 7599.0, 9109.0, 7424.0, 8419.0, 10381.0, 8687.0, 9621.0, 9784.0, 9412.0, 8381.0, 7336.0, 7797.0, 7774.0, 9743.0, 6613.0, 8421.0, 8991.0, 8040.0, 8642.0, 9346.0, 9398.0, 8152.0, 7069.0, 8492.0, 7482.0, 9238.0, 6617.0, 8395.0, 8951.0, 8879.0, 8976.0, 9162.0, 10055.0, 8957.0, 7264.0, 9399.0, 8179.0, 8856.0, 7573.0, 7629.0, 9630.0, 7488.0, 7364.0, 8514.0, 9340.0, 6537.0, 6821.0, 6508.0, 5058.0, 6445.0, 4729.0, 5293.0, 6118.0, 5740.0, 5493.0, 7046.0, 6898.0, 5735.0, 4985.0, 6703.0, 5659.0, 6987.0, 5688.0, 6417.0, 8004.0, 5997.0, 6437.0, 7944.0, 7297.0, 7376.0, 5944.0, 7523.0, 6404.0, 8260.0, 5610.0, 6563.0, 7652.0, 6705.0, 7465.0, 7628.0, 7026.0, 7328.0, 6476.0, 8681.0, 7185.0, 8528.0, 7048.0, 8306.0, 10101.0, 8361.0, 8175.0, 8690.0, 9388.0, 8581.0, 7751.0, 9293.0, 7947.0, 9505.0, 8143.0, 8924.0, 10723.0, 9401.0, 9430.0, 10071.0, 10696.0, 10388.0, 8776.0, 10528.0, 9139.0, 11238.0, 8729.0, 10064.0, 12519.0, 10313.0, 10189.0, 11840.0, 12023.0, 11176.0, 9594.0, 11667.0, 9877.0, 11755.0, 9290.0, 10034.0, 13030.0, 10366.0, 11192.0, 12474.0, 12684.0, 11620.0, 10245.0, 11893.0, 10313.0, 12110.0, 9939.0, 11180.0, 13699.0, 11570.0, 12677.0, 13884.0, 14709.0, 13740.0, 11225.0, 13823.0, 11785.0, 13869.0, 10635.0, 13132.0, 15985.0, 11631.0]\n"
     ]
    }
   ],
   "source": [
    "import xlrd\n",
    "import urllib\n",
    "import requests\n",
    "outfilename = \"sales.xls\"\n",
    "url_of_file = \"http://www.mia.org.nz/Portals/0/MIA-Sales%20Data/Vehicle%20Sales/1975%20Onwards%20Sales%20Series/Registration%20data%20-1975%20onwards%20July%202017.xlsx\"\n",
    "urllib.request.urlretrieve(url_of_file, outfilename)\n",
    "book = xlrd.open_workbook('sales.xls') # this file is in the same folder as this notebook\n",
    "first_sheet = book.sheet_by_index(2) # get the first sheet of the workbook\n",
    "\n",
    "print(first_sheet.row_values(0)) # get the first row of the first sheet\n",
    "print(first_sheet.row_values(1)) \n",
    "print(first_sheet.row_values(32))\n",
    "print(first_sheet.row_values(45))\n",
    "\n",
    "print(first_sheet.row_values(32)[2:14])\n",
    "print(first_sheet.row_values(45)[2:14])\n",
    "\n",
    "sales=[]\n",
    "for i in range(32,46):\n",
    "    sales=sales+first_sheet.row_values(i)[2:14]\n",
    "print(sales)\n",
    "sales=sales[0:163]\n",
    "print(sales)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['Category: Auto Insurance'], [], ['Month', 'Geo: New Zealand'], ['2004-01', '52'], ['2004-02', '58'], ['2004-03', '70'], ['2004-04', '70'], ['2004-05', '71'], ['2004-06', '77'], ['2004-07', '82'], ['2004-08', '75'], ['2004-09', '74'], ['2004-10', '73'], ['2004-11', '83'], ['2004-12', '67'], ['2005-01', '69'], ['2005-02', '92'], ['2005-03', '84'], ['2005-04', '84'], ['2005-05', '90'], ['2005-06', '86'], ['2005-07', '94'], ['2005-08', '89'], ['2005-09', '82'], ['2005-10', '83'], ['2005-11', '85'], ['2005-12', '67'], ['2006-01', '81'], ['2006-02', '76'], ['2006-03', '87'], ['2006-04', '86'], ['2006-05', '98'], ['2006-06', '92'], ['2006-07', '100'], ['2006-08', '92'], ['2006-09', '79'], ['2006-10', '84'], ['2006-11', '72'], ['2006-12', '59'], ['2007-01', '69'], ['2007-02', '76'], ['2007-03', '80'], ['2007-04', '71'], ['2007-05', '73'], ['2007-06', '75'], ['2007-07', '94'], ['2007-08', '76'], ['2007-09', '76'], ['2007-10', '67'], ['2007-11', '69'], ['2007-12', '57'], ['2008-01', '57'], ['2008-02', '62'], ['2008-03', '66'], ['2008-04', '68'], ['2008-05', '66'], ['2008-06', '67'], ['2008-07', '63'], ['2008-08', '65'], ['2008-09', '64'], ['2008-10', '54'], ['2008-11', '57'], ['2008-12', '43'], ['2009-01', '49'], ['2009-02', '61'], ['2009-03', '56'], ['2009-04', '56'], ['2009-05', '57'], ['2009-06', '62'], ['2009-07', '61'], ['2009-08', '66'], ['2009-09', '59'], ['2009-10', '58'], ['2009-11', '58'], ['2009-12', '52'], ['2010-01', '51'], ['2010-02', '60'], ['2010-03', '62'], ['2010-04', '57'], ['2010-05', '61'], ['2010-06', '60'], ['2010-07', '56'], ['2010-08', '62'], ['2010-09', '63'], ['2010-10', '46'], ['2010-11', '51'], ['2010-12', '43'], ['2011-01', '62'], ['2011-02', '67'], ['2011-03', '76'], ['2011-04', '71'], ['2011-05', '78'], ['2011-06', '74'], ['2011-07', '68'], ['2011-08', '70'], ['2011-09', '59'], ['2011-10', '59'], ['2011-11', '60'], ['2011-12', '55'], ['2012-01', '57'], ['2012-02', '64'], ['2012-03', '64'], ['2012-04', '61'], ['2012-05', '73'], ['2012-06', '67'], ['2012-07', '69'], ['2012-08', '71'], ['2012-09', '59'], ['2012-10', '59'], ['2012-11', '59'], ['2012-12', '52'], ['2013-01', '57'], ['2013-02', '61'], ['2013-03', '59'], ['2013-04', '61'], ['2013-05', '68'], ['2013-06', '66'], ['2013-07', '71'], ['2013-08', '69'], ['2013-09', '69'], ['2013-10', '65'], ['2013-11', '61'], ['2013-12', '55'], ['2014-01', '62'], ['2014-02', '65'], ['2014-03', '69'], ['2014-04', '62'], ['2014-05', '68'], ['2014-06', '71'], ['2014-07', '69'], ['2014-08', '69'], ['2014-09', '66'], ['2014-10', '61'], ['2014-11', '65'], ['2014-12', '58'], ['2015-01', '62'], ['2015-02', '70'], ['2015-03', '74'], ['2015-04', '71'], ['2015-05', '70'], ['2015-06', '74'], ['2015-07', '73'], ['2015-08', '72'], ['2015-09', '72'], ['2015-10', '65'], ['2015-11', '67'], ['2015-12', '58'], ['2016-01', '61'], ['2016-02', '71'], ['2016-03', '71'], ['2016-04', '70'], ['2016-05', '75'], ['2016-06', '73'], ['2016-07', '76'], ['2016-08', '74'], ['2016-09', '70'], ['2016-10', '68'], ['2016-11', '78'], ['2016-12', '64'], ['2017-01', '68'], ['2017-02', '76'], ['2017-03', '78'], ['2017-04', '74'], ['2017-05', '75'], ['2017-06', '76'], ['2017-07', '77'], ['2017-08', '70']]\n",
      "[52.0, 58.0, 70.0, 70.0, 71.0, 77.0, 82.0, 75.0, 74.0, 73.0, 83.0, 67.0, 69.0, 92.0, 84.0, 84.0, 90.0, 86.0, 94.0, 89.0, 82.0, 83.0, 85.0, 67.0, 81.0, 76.0, 87.0, 86.0, 98.0, 92.0, 100.0, 92.0, 79.0, 84.0, 72.0, 59.0, 69.0, 76.0, 80.0, 71.0, 73.0, 75.0, 94.0, 76.0, 76.0, 67.0, 69.0, 57.0, 57.0, 62.0, 66.0, 68.0, 66.0, 67.0, 63.0, 65.0, 64.0, 54.0, 57.0, 43.0, 49.0, 61.0, 56.0, 56.0, 57.0, 62.0, 61.0, 66.0, 59.0, 58.0, 58.0, 52.0, 51.0, 60.0, 62.0, 57.0, 61.0, 60.0, 56.0, 62.0, 63.0, 46.0, 51.0, 43.0, 62.0, 67.0, 76.0, 71.0, 78.0, 74.0, 68.0, 70.0, 59.0, 59.0, 60.0, 55.0, 57.0, 64.0, 64.0, 61.0, 73.0, 67.0, 69.0, 71.0, 59.0, 59.0, 59.0, 52.0, 57.0, 61.0, 59.0, 61.0, 68.0, 66.0, 71.0, 69.0, 69.0, 65.0, 61.0, 55.0, 62.0, 65.0, 69.0, 62.0, 68.0, 71.0, 69.0, 69.0, 66.0, 61.0, 65.0, 58.0, 62.0, 70.0, 74.0, 71.0, 70.0, 74.0, 73.0, 72.0, 72.0, 65.0, 67.0, 58.0, 61.0, 71.0, 71.0, 70.0, 75.0, 73.0, 76.0, 74.0, 70.0, 68.0, 78.0, 64.0, 68.0, 76.0, 78.0, 74.0, 75.0, 76.0, 77.0, 70.0]\n"
     ]
    }
   ],
   "source": [
    "# data downloaded from Google Trends\n",
    "import csv\n",
    "with open(\"Auto Insurance NZ 18 08 2017.csv\", 'r') as f:\n",
    "  reader = csv.reader(f)\n",
    "  AutoIns = list(reader)\n",
    "print(AutoIns)\n",
    "AI=[]\n",
    "for i in range(3,len(AutoIns)):\n",
    "    AI=AI+[float(AutoIns[i][1])]\n",
    "print(AI) # note this includes august 2017!\n",
    "AI=AI[0:len(AI)-1] # to get til July 2017\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['Category: Trucks & SUVs'], [], ['Month', 'Geo: New Zealand'], ['2004-01', '77'], ['2004-02', '94'], ['2004-03', '72'], ['2004-04', '78'], ['2004-05', '95'], ['2004-06', '90'], ['2004-07', '90'], ['2004-08', '89'], ['2004-09', '76'], ['2004-10', '94'], ['2004-11', '91'], ['2004-12', '75'], ['2005-01', '82'], ['2005-02', '85'], ['2005-03', '94'], ['2005-04', '83'], ['2005-05', '90'], ['2005-06', '79'], ['2005-07', '100'], ['2005-08', '83'], ['2005-09', '83'], ['2005-10', '76'], ['2005-11', '79'], ['2005-12', '66'], ['2006-01', '73'], ['2006-02', '67'], ['2006-03', '74'], ['2006-04', '72'], ['2006-05', '79'], ['2006-06', '77'], ['2006-07', '78'], ['2006-08', '76'], ['2006-09', '69'], ['2006-10', '74'], ['2006-11', '66'], ['2006-12', '58'], ['2007-01', '62'], ['2007-02', '65'], ['2007-03', '70'], ['2007-04', '66'], ['2007-05', '72'], ['2007-06', '71'], ['2007-07', '69'], ['2007-08', '68'], ['2007-09', '64'], ['2007-10', '64'], ['2007-11', '64'], ['2007-12', '48'], ['2008-01', '54'], ['2008-02', '63'], ['2008-03', '57'], ['2008-04', '59'], ['2008-05', '56'], ['2008-06', '65'], ['2008-07', '60'], ['2008-08', '67'], ['2008-09', '61'], ['2008-10', '55'], ['2008-11', '55'], ['2008-12', '50'], ['2009-01', '51'], ['2009-02', '51'], ['2009-03', '51'], ['2009-04', '54'], ['2009-05', '61'], ['2009-06', '59'], ['2009-07', '62'], ['2009-08', '70'], ['2009-09', '62'], ['2009-10', '57'], ['2009-11', '58'], ['2009-12', '46'], ['2010-01', '50'], ['2010-02', '50'], ['2010-03', '57'], ['2010-04', '53'], ['2010-05', '60'], ['2010-06', '62'], ['2010-07', '48'], ['2010-08', '56'], ['2010-09', '46'], ['2010-10', '45'], ['2010-11', '48'], ['2010-12', '38'], ['2011-01', '52'], ['2011-02', '60'], ['2011-03', '69'], ['2011-04', '63'], ['2011-05', '73'], ['2011-06', '67'], ['2011-07', '67'], ['2011-08', '69'], ['2011-09', '61'], ['2011-10', '59'], ['2011-11', '60'], ['2011-12', '52'], ['2012-01', '54'], ['2012-02', '61'], ['2012-03', '64'], ['2012-04', '61'], ['2012-05', '69'], ['2012-06', '68'], ['2012-07', '66'], ['2012-08', '76'], ['2012-09', '72'], ['2012-10', '63'], ['2012-11', '62'], ['2012-12', '59'], ['2013-01', '57'], ['2013-02', '60'], ['2013-03', '64'], ['2013-04', '65'], ['2013-05', '64'], ['2013-06', '61'], ['2013-07', '67'], ['2013-08', '66'], ['2013-09', '64'], ['2013-10', '60'], ['2013-11', '59'], ['2013-12', '57'], ['2014-01', '60'], ['2014-02', '63'], ['2014-03', '62'], ['2014-04', '67'], ['2014-05', '67'], ['2014-06', '67'], ['2014-07', '67'], ['2014-08', '71'], ['2014-09', '64'], ['2014-10', '63'], ['2014-11', '64'], ['2014-12', '61'], ['2015-01', '70'], ['2015-02', '67'], ['2015-03', '68'], ['2015-04', '67'], ['2015-05', '75'], ['2015-06', '73'], ['2015-07', '70'], ['2015-08', '74'], ['2015-09', '73'], ['2015-10', '67'], ['2015-11', '72'], ['2015-12', '66'], ['2016-01', '73'], ['2016-02', '68'], ['2016-03', '68'], ['2016-04', '71'], ['2016-05', '76'], ['2016-06', '78'], ['2016-07', '75'], ['2016-08', '77'], ['2016-09', '74'], ['2016-10', '69'], ['2016-11', '67'], ['2016-12', '67'], ['2017-01', '75'], ['2017-02', '74'], ['2017-03', '77'], ['2017-04', '77'], ['2017-05', '79'], ['2017-06', '78'], ['2017-07', '78'], ['2017-08', '79']]\n",
      "[77.0, 94.0, 72.0, 78.0, 95.0, 90.0, 90.0, 89.0, 76.0, 94.0, 91.0, 75.0, 82.0, 85.0, 94.0, 83.0, 90.0, 79.0, 100.0, 83.0, 83.0, 76.0, 79.0, 66.0, 73.0, 67.0, 74.0, 72.0, 79.0, 77.0, 78.0, 76.0, 69.0, 74.0, 66.0, 58.0, 62.0, 65.0, 70.0, 66.0, 72.0, 71.0, 69.0, 68.0, 64.0, 64.0, 64.0, 48.0, 54.0, 63.0, 57.0, 59.0, 56.0, 65.0, 60.0, 67.0, 61.0, 55.0, 55.0, 50.0, 51.0, 51.0, 51.0, 54.0, 61.0, 59.0, 62.0, 70.0, 62.0, 57.0, 58.0, 46.0, 50.0, 50.0, 57.0, 53.0, 60.0, 62.0, 48.0, 56.0, 46.0, 45.0, 48.0, 38.0, 52.0, 60.0, 69.0, 63.0, 73.0, 67.0, 67.0, 69.0, 61.0, 59.0, 60.0, 52.0, 54.0, 61.0, 64.0, 61.0, 69.0, 68.0, 66.0, 76.0, 72.0, 63.0, 62.0, 59.0, 57.0, 60.0, 64.0, 65.0, 64.0, 61.0, 67.0, 66.0, 64.0, 60.0, 59.0, 57.0, 60.0, 63.0, 62.0, 67.0, 67.0, 67.0, 67.0, 71.0, 64.0, 63.0, 64.0, 61.0, 70.0, 67.0, 68.0, 67.0, 75.0, 73.0, 70.0, 74.0, 73.0, 67.0, 72.0, 66.0, 73.0, 68.0, 68.0, 71.0, 76.0, 78.0, 75.0, 77.0, 74.0, 69.0, 67.0, 67.0, 75.0, 74.0, 77.0, 77.0, 79.0, 78.0, 78.0, 79.0]\n",
      "164\n",
      "163\n",
      "[77.0, 94.0, 72.0, 78.0, 95.0, 90.0, 90.0, 89.0, 76.0, 94.0, 91.0, 75.0, 82.0, 85.0, 94.0, 83.0, 90.0, 79.0, 100.0, 83.0, 83.0, 76.0, 79.0, 66.0, 73.0, 67.0, 74.0, 72.0, 79.0, 77.0, 78.0, 76.0, 69.0, 74.0, 66.0, 58.0, 62.0, 65.0, 70.0, 66.0, 72.0, 71.0, 69.0, 68.0, 64.0, 64.0, 64.0, 48.0, 54.0, 63.0, 57.0, 59.0, 56.0, 65.0, 60.0, 67.0, 61.0, 55.0, 55.0, 50.0, 51.0, 51.0, 51.0, 54.0, 61.0, 59.0, 62.0, 70.0, 62.0, 57.0, 58.0, 46.0, 50.0, 50.0, 57.0, 53.0, 60.0, 62.0, 48.0, 56.0, 46.0, 45.0, 48.0, 38.0, 52.0, 60.0, 69.0, 63.0, 73.0, 67.0, 67.0, 69.0, 61.0, 59.0, 60.0, 52.0, 54.0, 61.0, 64.0, 61.0, 69.0, 68.0, 66.0, 76.0, 72.0, 63.0, 62.0, 59.0, 57.0, 60.0, 64.0, 65.0, 64.0, 61.0, 67.0, 66.0, 64.0, 60.0, 59.0, 57.0, 60.0, 63.0, 62.0, 67.0, 67.0, 67.0, 67.0, 71.0, 64.0, 63.0, 64.0, 61.0, 70.0, 67.0, 68.0, 67.0, 75.0, 73.0, 70.0, 74.0, 73.0, 67.0, 72.0, 66.0, 73.0, 68.0, 68.0, 71.0, 76.0, 78.0, 75.0, 77.0, 74.0, 69.0, 67.0, 67.0, 75.0, 74.0, 77.0, 77.0, 79.0, 78.0, 78.0]\n"
     ]
    }
   ],
   "source": [
    "# data downloaded from Google Trends\n",
    "import csv\n",
    "with open(\"Trucks and SUVs NZ 18 08 2017.csv\", 'r') as f:\n",
    "  reader = csv.reader(f)\n",
    "  TrucksSUVs = list(reader)\n",
    "print( TrucksSUVs)\n",
    "TS=[]\n",
    "for i in range(3,len(TrucksSUVs)):\n",
    "    TS=TS+[float(TrucksSUVs[i][1])]\n",
    "print(TS) # note this includes august 2017!\n",
    "print(len(TS))\n",
    "TS=TS[0:len(TS)-1] # to get til July 2017\n",
    "print(len(TS))\n",
    "print(TS) # "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "163\n",
      "163\n"
     ]
    }
   ],
   "source": [
    "print(len(TS))\n",
    "print(len(sales))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     const        AI        TS  lagged sales 1  lagged sales 12\n",
      "0      1.0  3.951244  4.343805             NaN              NaN\n",
      "1      1.0  4.060443  4.543295        8.886686              NaN\n",
      "2      1.0  4.248495  4.276666        8.842316              NaN\n",
      "3      1.0  4.248495  4.356709        9.084777              NaN\n",
      "4      1.0  4.262680  4.553877        8.860357              NaN\n",
      "5      1.0  4.343805  4.499810        8.953899              NaN\n",
      "6      1.0  4.406719  4.499810        9.183791              NaN\n",
      "7      1.0  4.317488  4.488636        9.047115              NaN\n",
      "8      1.0  4.304065  4.330733        8.972717              NaN\n",
      "9      1.0  4.290459  4.543295        9.129672              NaN\n",
      "10     1.0  4.418841  4.510860        9.125436              NaN\n",
      "11     1.0  4.204693  4.317488        9.076580              NaN\n",
      "12     1.0  4.234107  4.406719        8.956093         8.886686\n",
      "13     1.0  4.521789  4.442651        8.894944         8.842316\n",
      "14     1.0  4.430817  4.543295        8.935772         9.084777\n",
      "15     1.0  4.430817  4.418841        9.117018         8.860357\n",
      "16     1.0  4.499810  4.499810        8.912473         8.953899\n",
      "17     1.0  4.454347  4.369448        9.038246         9.183791\n",
      "18     1.0  4.543295  4.605170        9.247732         9.047115\n",
      "19     1.0  4.488636  4.418841        9.069583         8.972717\n",
      "20     1.0  4.406719  4.418841        9.171703         9.129672\n",
      "21     1.0  4.418841  4.330733        9.188504         9.125436\n",
      "22     1.0  4.442651  4.369448        9.149741         9.076580\n",
      "23     1.0  4.204693  4.189655        9.033723         8.956093\n",
      "24     1.0  4.394449  4.290459        8.900549         8.894944\n",
      "25     1.0  4.330733  4.204693        8.961494         8.935772\n",
      "26     1.0  4.465908  4.304065        8.958540         9.117018\n",
      "27     1.0  4.454347  4.276666        9.184304         8.912473\n",
      "28     1.0  4.584967  4.369448        8.796793         9.038246\n",
      "29     1.0  4.521789  4.343805        9.038484         9.247732\n",
      "..     ...       ...       ...             ...              ...\n",
      "133    1.0  4.248495  4.204693        9.364520         9.120306\n",
      "134    1.0  4.304065  4.219508        9.197964         9.327056\n",
      "135    1.0  4.262680  4.204693        9.372034         9.074406\n",
      "136    1.0  4.248495  4.317488        9.136694         9.216720\n",
      "137    1.0  4.304065  4.290459        9.213735         9.435003\n",
      "138    1.0  4.290459  4.248495        9.475010         9.241161\n",
      "139    1.0  4.276666  4.304065        9.246286         9.229064\n",
      "140    1.0  4.276666  4.290459        9.322955         9.379239\n",
      "141    1.0  4.174387  4.204693        9.431402         9.394577\n",
      "142    1.0  4.204693  4.276666        9.448097         9.321524\n",
      "143    1.0  4.060443  4.189655        9.360483         9.168893\n",
      "144    1.0  4.110874  4.290459        9.234545         9.364520\n",
      "145    1.0  4.262680  4.219508        9.383705         9.197964\n",
      "146    1.0  4.262680  4.219508        9.241161         9.372034\n",
      "147    1.0  4.248495  4.262680        9.401787         9.136694\n",
      "148    1.0  4.317488  4.330733        9.204222         9.213735\n",
      "149    1.0  4.290459  4.356709        9.321882         9.475010\n",
      "150    1.0  4.330733  4.317488        9.525078         9.246286\n",
      "151    1.0  4.304065  4.343805        9.356171         9.322955\n",
      "152    1.0  4.248495  4.304065        9.447545         9.431402\n",
      "153    1.0  4.219508  4.234107        9.538492         9.448097\n",
      "154    1.0  4.356709  4.204693        9.596215         9.360483\n",
      "155    1.0  4.158883  4.204693        9.528067         9.234545\n",
      "156    1.0  4.219508  4.317488        9.325899         9.383705\n",
      "157    1.0  4.330733  4.304065        9.534089         9.241161\n",
      "158    1.0  4.356709  4.343805        9.374583         9.401787\n",
      "159    1.0  4.304065  4.343805        9.537411         9.204222\n",
      "160    1.0  4.317488  4.369448        9.271906         9.321882\n",
      "161    1.0  4.330733  4.356709        9.482807         9.525078\n",
      "162    1.0  4.343805  4.356709        9.679406         9.356171\n",
      "\n",
      "[163 rows x 5 columns]\n",
      "                            OLS Regression Results                            \n",
      "==============================================================================\n",
      "Dep. Variable:                  sales   R-squared:                       0.778\n",
      "Model:                            OLS   Adj. R-squared:                  0.772\n",
      "Method:                 Least Squares   F-statistic:                     128.0\n",
      "Date:                Tue, 12 Sep 2017   Prob (F-statistic):           1.08e-46\n",
      "Time:                        09:13:01   Log-Likelihood:                 111.55\n",
      "No. Observations:                 151   AIC:                            -213.1\n",
      "Df Residuals:                     146   BIC:                            -198.0\n",
      "Df Model:                           4                                         \n",
      "Covariance Type:            nonrobust                                         \n",
      "===================================================================================\n",
      "                      coef    std err          t      P>|t|      [0.025      0.975]\n",
      "-----------------------------------------------------------------------------------\n",
      "const              -0.5113      0.443     -1.153      0.251      -1.387       0.365\n",
      "AI                 -0.0647      0.112     -0.575      0.566      -0.287       0.158\n",
      "TS                  0.2159      0.125      1.729      0.086      -0.031       0.463\n",
      "lagged sales 1      0.4002      0.051      7.842      0.000       0.299       0.501\n",
      "lagged sales 12     0.5893      0.060      9.848      0.000       0.471       0.708\n",
      "==============================================================================\n",
      "Omnibus:                       15.594   Durbin-Watson:                   1.581\n",
      "Prob(Omnibus):                  0.000   Jarque-Bera (JB):               17.541\n",
      "Skew:                          -0.722   Prob(JB):                     0.000155\n",
      "Kurtosis:                       3.838   Cond. No.                         659.\n",
      "==============================================================================\n",
      "\n",
      "Warnings:\n",
      "[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.\n"
     ]
    }
   ],
   "source": [
    "# first for the whole period\n",
    "import statsmodels.api as sm\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "AI=np.log(AI)\n",
    "TS=np.log(TS)\n",
    "sales=np.log(sales)\n",
    "x1 = pd.Series(AI, name='AI') \n",
    "x2=pd.Series(TS, name='TS')\n",
    "x=pd.concat([x1, x2], axis=1)\n",
    "x=sm.add_constant(x)\n",
    "y = pd.Series(sales, name='sales') \n",
    "lag=y.shift(+1)\n",
    "x=pd.concat([x, lag], axis=1)\n",
    "x = x.rename(columns={'sales': 'lagged sales 1'})\n",
    "lag=y.shift(+12)\n",
    "x=pd.concat([x, lag], axis=1)\n",
    "x = x.rename(columns={'sales': 'lagged sales 12'})\n",
    "\n",
    "print(x)\n",
    "model = sm.OLS(y, x, missing='drop')\n",
    "results = model.fit()\n",
    "print(results.summary())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            OLS Regression Results                            \n",
      "==============================================================================\n",
      "Dep. Variable:                  sales   R-squared:                       0.770\n",
      "Model:                            OLS   Adj. R-squared:                  0.767\n",
      "Method:                 Least Squares   F-statistic:                     248.4\n",
      "Date:                Tue, 12 Sep 2017   Prob (F-statistic):           5.04e-48\n",
      "Time:                        09:13:01   Log-Likelihood:                 109.00\n",
      "No. Observations:                 151   AIC:                            -212.0\n",
      "Df Residuals:                     148   BIC:                            -203.0\n",
      "Df Model:                           2                                         \n",
      "Covariance Type:            nonrobust                                         \n",
      "===================================================================================\n",
      "                      coef    std err          t      P>|t|      [0.025      0.975]\n",
      "-----------------------------------------------------------------------------------\n",
      "const              -0.4556      0.428     -1.064      0.289      -1.302       0.391\n",
      "lagged sales 1      0.4225      0.051      8.361      0.000       0.323       0.522\n",
      "lagged sales 12     0.6304      0.058     10.959      0.000       0.517       0.744\n",
      "==============================================================================\n",
      "Omnibus:                       22.234   Durbin-Watson:                   1.612\n",
      "Prob(Omnibus):                  0.000   Jarque-Bera (JB):               29.109\n",
      "Skew:                          -0.860   Prob(JB):                     4.78e-07\n",
      "Kurtosis:                       4.292   Cond. No.                         571.\n",
      "==============================================================================\n",
      "\n",
      "Warnings:\n",
      "[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.\n"
     ]
    }
   ],
   "source": [
    "# AR terms only model\n",
    "lagsonlyx=x[[\"const\", \"lagged sales 1\",\"lagged sales 12\"]]\n",
    "model = sm.OLS(y, lagsonlyx, missing='drop')\n",
    "results = model.fit()\n",
    "print(results.summary())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            OLS Regression Results                            \n",
      "==============================================================================\n",
      "Dep. Variable:                  sales   R-squared:                       0.496\n",
      "Model:                            OLS   Adj. R-squared:                  0.482\n",
      "Method:                 Least Squares   F-statistic:                     37.35\n",
      "Date:                Tue, 12 Sep 2017   Prob (F-statistic):           5.05e-12\n",
      "Time:                        09:13:01   Log-Likelihood:                 48.314\n",
      "No. Observations:                  79   AIC:                            -90.63\n",
      "Df Residuals:                      76   BIC:                            -83.52\n",
      "Df Model:                           2                                         \n",
      "Covariance Type:            nonrobust                                         \n",
      "===================================================================================\n",
      "                      coef    std err          t      P>|t|      [0.025      0.975]\n",
      "-----------------------------------------------------------------------------------\n",
      "const               1.1283      0.904      1.249      0.216      -0.671       2.928\n",
      "lagged sales 1      0.3956      0.086      4.602      0.000       0.224       0.567\n",
      "lagged sales 12     0.4765      0.087      5.505      0.000       0.304       0.649\n",
      "==============================================================================\n",
      "Omnibus:                        7.716   Durbin-Watson:                   1.786\n",
      "Prob(Omnibus):                  0.021   Jarque-Bera (JB):                7.312\n",
      "Skew:                          -0.726   Prob(JB):                       0.0258\n",
      "Kurtosis:                       3.334   Cond. No.                         763.\n",
      "==============================================================================\n",
      "\n",
      "Warnings:\n",
      "[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.\n"
     ]
    }
   ],
   "source": [
    "# for period like Choi Varian\n",
    "# in the paper the dataset only goes til july 2011 - hence we cut our longer dataset back to jan 2014 -july 2011\n",
    "shortx=x[:91]\n",
    "lagsonlyx=shortx[[\"const\", \"lagged sales 1\",\"lagged sales 12\"]]\n",
    "shorty=y[:91]\n",
    "model = sm.OLS(shorty, lagsonlyx, missing='drop')\n",
    "results = model.fit()\n",
    "print(results.summary())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            OLS Regression Results                            \n",
      "==============================================================================\n",
      "Dep. Variable:                  sales   R-squared:                       0.552\n",
      "Model:                            OLS   Adj. R-squared:                  0.528\n",
      "Method:                 Least Squares   F-statistic:                     22.84\n",
      "Date:                Tue, 12 Sep 2017   Prob (F-statistic):           2.58e-12\n",
      "Time:                        09:13:01   Log-Likelihood:                 53.035\n",
      "No. Observations:                  79   AIC:                            -96.07\n",
      "Df Residuals:                      74   BIC:                            -84.22\n",
      "Df Model:                           4                                         \n",
      "Covariance Type:            nonrobust                                         \n",
      "===================================================================================\n",
      "                      coef    std err          t      P>|t|      [0.025      0.975]\n",
      "-----------------------------------------------------------------------------------\n",
      "const               1.5787      0.875      1.803      0.075      -0.166       3.323\n",
      "AI                  0.2428      0.165      1.472      0.145      -0.086       0.571\n",
      "TS                  0.0243      0.170      0.143      0.887      -0.314       0.363\n",
      "lagged sales 1      0.2984      0.088      3.382      0.001       0.123       0.474\n",
      "lagged sales 12     0.3973      0.088      4.535      0.000       0.223       0.572\n",
      "==============================================================================\n",
      "Omnibus:                        5.160   Durbin-Watson:                   1.789\n",
      "Prob(Omnibus):                  0.076   Jarque-Bera (JB):                4.542\n",
      "Skew:                          -0.574   Prob(JB):                        0.103\n",
      "Kurtosis:                       3.248   Cond. No.                         855.\n",
      "==============================================================================\n",
      "\n",
      "Warnings:\n",
      "[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.\n"
     ]
    }
   ],
   "source": [
    "# now lets see what adding the search terms adds to R2, for the shorter period\n",
    "shortx=x[:91]\n",
    "shorty=y[:91]\n",
    "model = sm.OLS(shorty, shortx, missing='drop')\n",
    "results = model.fit()\n",
    "print(results.summary())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
